The present invention generally relates to a user interface for a spreadsheet program, and more specifically, to an intuitive interface that facilitates the creation of a crosstab data table to display data within a spreadsheet program.
Crosstab data tables designated as PivotTable(trademark) data displays have been provided in Microsoft Corporation""s EXCEL(trademark) spreadsheet program for several years. (PivotTable is a trademark of Microsoft Corporation.) A PivotTable data display is a type of crosstab data table and provides an efficient way to display and summarize data that are supplied by a database program or which are in a data listing of a spreadsheet. A user can select fields of the data to include within page, row, column, or data regions of the PivotTable data display and can choose parameters such as the sum, variance, count, and standard deviation to be displayed for selected data fields. Relatively structured spreadsheets that already have subtotals, data entry cells, and summaries of fields are generally not appropriately expressed using a PivotTable data display. However, any data in a database that can be queried from within a spreadsheet program,. or spreadsheet data comprising lists that are not already organized to include sums and other calculated values are ideal candidates for a PivotTable data display.
Users of the prior art EXCEL spreadsheet program who have attempted to apply a PivotTable data display to data have been frustrated by difficulties they have experienced. The most recent prior art version of the EXCEL spreadsheet program (EXCEL 97) includes a PivotTable Wizard that can be used to create a PivotTable data display in four steps. While the four steps used by the Wizard to create a PivotTable data display seemed simple enough, users have not found it intuitive to make changes to a PivotTable data display after it has been produced with the assistance of the Wizard. In fact, changes to a PivotTable data display could most easily be made in this prior art program by again opening the PivotTable Wizard. The PivotTable Wizard of this prior EXCEL spreadsheet program employs a modal drag-and-drop dialog representation of a PivotTable data display to enable a user to select the fields to be included in regions of the PivotTable data display. However, the dialog that includes this drag-and-drop representation is not accessible until the PivotTable Wizard is invoked. It is not very intuitive to run the Wizard to access the drag-and-drop representation of the PivotTable data display in this prior art program.
It will be apparent that it would be preferable to enable users to produce and modify a PivotTable data display by dragging and dropping fields directly on a PivotTable data display on a spreadsheet, rather than in a modal dialog PivotTable representation. By providing direct access to the PivotTable data display for making drag-and-drop changes, it should be much clearer how a user might modify a PivotTable data display.
In addition, when dragging a field over a PivotTable data display, it would be useful to display an icon associated with the field that more clearly indicates the region of the PivotTable data display over which the field is then disposed that was available in the prior art. If the,user optionally can always access fields that can be dragged onto a spreadsheet PivotTable data display and can more readily determine the region over which the fields are disposed during the dragging process, much of the confusion of the prior art in regard to creating and modifying a PivotTable data display should be alleviated.
Certain types of data fields that can be used to create a PivotTable data display are of the xe2x80x9conline analysis processingxe2x80x9d (OLAP) type. OLAP data include field that can only be placed in the row, column, or page regions of a PivotTable data display and are referred to as xe2x80x9cdimensionxe2x80x9d fields, and fields that can only be placed in the data region of a PivotTable data display and are thus referred to as xe2x80x9cmeasurexe2x80x9d fields. The prior art spreadsheet programs have not recognized OLAP data fields and have not indicated to the user the type of OLAP field, or the specific region in which such fields can be inserted. It would be preferable to provide a clear indication of whether fields are of the OLAP type and an indication of whether a specific field is a dimension or a measure field. Prohibitions against using dimension fields within the data region, or measure fields within any of the row, column, or page regions of a PivotTable data display should be enforced in such a way as to enable a user to understand where a field being dragged onto a PivotTable data display can be dropped.
In accord with the present invention, a method is defined for enabling a user to selectively produce and modify a spreadsheet PivotTable. data display using a specified set of data. The method includes the step of displaying an array of field buttons on a spreadsheet, each field button in the array representing a different field of the specified set of data. Predefined regions of the PivotTable data display are represented directly on the spreadsheet to facilitate enabling a user to graphically select a field button from the array, drag the field button that was selected over a region of the PivotTable data display on the spreadsheet in which the user desires to include a field represented by the field button that was selected, and drop the field button that was selected. When the user drops the field button, the field represented by the field button appears within the region in which the field button was dropped. The preceding steps are repeated for any of the fields represented by the field buttons that the user desires to include in the PivotTable data display. The PivotTable data display presented to the user thereby includes the fields within the regions in which the user dropped each field button.
The regions in which a field button can be dropped include a row region, a column region, a page region, and a data region. A user can change the PivotTable data display by graphically selecting a field button in the array, dragging the field button over the region of the PivotTable data display on the spreadsheet in which the user desires to include a field represented by the field button that was just selected, and dropping the field button that was selected, causing the field represented by that field button to be included within the region in which the field button was dropped. Similarly, a user can change the PivotTable data display by graphically selecting a field in the PivotTable data display, dragging a field button representing the field that was just selected either from the PivotTable data display, or over a different region of the PivotTable data display, and dropping the field. If the field button is dragged off the PivotTable .data display, the field represented by that field button is deleted from the PivotTable data display, and if the field button is dropped in a different region, the field represented by that field button is moved to the different region of the PivotTable data display.
A user can also change a position of a field in a region relative to another field in the region by dragging a field button representing the field to be changed to a different position in the same region. The relative positions are changed only if the region in which the field is dragged is a page region, a row region, or a column region.
The method also preferably includes the step of employing a cursor shape to clearly graphically indicate a region of the PivotTable data display over which a field button is currently being dragged. A shape of the cursor used to graphically select a field button is changed when a user selects the field button from the array. In addition, the method preferably includes the step of graphically highlighting a region of the PivotTable data display over which the field button is being dragged.
In one preferred form of the invention, the array remains visible at all times, unless closed by a user, to enable changes and additions to be made to the PivotTable data display. Icons are preferably included in the array to indicate a field type for data that comprise OLAP fields; the field type is either a dimension or a measure. A user is precluded from dropping a field button representing a dimension field into a data region on the PivotTable data display, or dropping a field button representing a measure field into either a page region, a row region, or a column region.
Another aspect of the present invention is directed to a computer-readable medium having computer-executable instructions for performing the steps of the method discussed above.
Still another aspect of the present invention is directed to a system that includes a memory in which machine instructions are stored, a display, and a processor coupled to the memory and the display. The processor implements the machine instructions to execute a spreadsheet program and carries out functions in response to the machine instructions that enable a user to create and modify a PivotTable data display generally as described above.